<?php
namespace lib\db;

use lib\Exception;
use lib\Db;

class Query{

    /**
     * @var \Mysqli
     */
    protected $connection;

    /**
     * @var array
     */
    protected $factors = ['fields'=>'*'];

    /**
     * Query constructor.
     * @param \Mysqli|null $connection
     * @throws \Exception
     */
    function __construct(\Mysqli $connection = null){
        $this->connection = $connection ? $connection : Db::connect();
    }

    /**
     * 转义
     * @param $value
     * @return string
     */
    function quote($value){
        if(empty($value)){
            return $value;
        }
        return addslashes(htmlspecialchars($value, ENT_QUOTES));
    }

    /**
     * 解析Field
     * @param $field
     * @return string
     */
    function parseField($field){
        $fieldArr = explode(' ', $field);
        foreach ($fieldArr as $key => $val){
            if(!in_array(strtolower($val), ['as'])){
                $arr = explode('.', $val);
                foreach ($arr as $k => $v){
                    if(!in_array($v, ['*', '='])){
                        $arr[$k] = "`{$this->quote($v)}`";
                    }
                }
                $fieldArr[$key] = implode('.', $arr);
            }else{
                $fieldArr[$key] = strtoupper($val);
            }
        }
        return implode(' ', $fieldArr);
    }

    /**
     * 解析Value
     * @param $value
     * @return string
     */
    function parseValue($value){
        if(is_array($value)){
            $cache = array();
            foreach ($value as $item){
                array_push($cache, "'{$this->quote($item)}'");
            }
            $value = implode(',', $cache);
        }else{
            $value = "'{$this->quote($value)}'";
        }
        return $value;
    }

    /**
     * 指定表名
     * @access public
     * @param string $name
     * @return $this
     */
    function name($name){
        $this->factors['table'] = $this->parseField($name);
        return $this;
    }

    /**
     * 设置字段
     * @param $field
     * @return $this
     */
    function field($field){
        if(is_string($field)){
            $field = explode(',', $field);
        }
        $cache = array();
        foreach ($field as $item){
            $item = $this->parseField(trim($item));
            array_push($cache, $item);
        }
        $this->factors['fields'] = implode(',', $cache);
        return $this;
    }

    /**
     * 设置WHERE条件
     * @param $where
     * @param string $condition
     * @return $this
     */
    function where($where, $condition = 'AND'){
        if(is_array($where)){
            $cache = array();
            foreach ($where as $key => $val){
                if(is_array($val)){
                    foreach ($val as $k => $v){
                        switch ($k){
                            case 'not between':
                                array_push($cache, $this->parseField($key) . " NOT BETWEEN {$v[0]} AND {$v[1]}");
                                break;
                            case 'between':
                                array_push($cache, $this->parseField($key) . " BETWEEN {$v[0]} AND {$v[1]}");
                                break;
                            case 'not in':
                                array_push($cache, $this->parseField($key) . " NOT IN (" . $this->parseValue($v) . ')');
                                break;
                            case 'in':
                                array_push($cache, $this->parseField($key) . " IN (" . $this->parseValue($v) . ')');
                                break;
                            default:
                                array_push($cache, $this->parseField($key) . " {$k} " . $this->parseValue($v));
                                break;
                        }
                    }
                }else{
                    array_push($cache, $this->parseField($key) . " = " . $this->parseValue($val));
                }
            }
            $this->factors['where'] = implode(" {$condition} ", $cache);
        }else{
            $this->factors['where'] = $where;
        }
        return $this;
    }

    /**
     * 设置GROUP条件
     * @param $field
     * @return $this
     */
    function group($field){
        $this->factors['group'] = $this->parseField($field);
        return $this;
    }

    /**
     * 设置JOIN条件
     * @param $table
     * @param $on
     * @param string $way
     * @return $this
     */
    function join($table, $on, $way = 'LEFT'){
        $table = $this->parseField($table);
        $on = $this->parseField($on);
        $this->factors['join'][] = "{$way} JOIN {$table} ON {$on}";
        return $this;
    }

    /**
     * 设置ORDER条件
     * @param $field
     * @param string $way
     * @return $this
     */
    function order($field, $way = 'DESC'){
        if(stripos($field, ' ')){
            $cache = explode(' ', $field);
            $field = $cache[0];
            $way = $cache[1];
        }
        $way = strtoupper($way);
        $this->factors['order'][] = $this->parseField($field) . " {$way}";
        return $this;
    }

    /**
     * 设置LIMIT条件
     * @param $start
     * @param $end
     * @return $this
     */
    function limit($start, $end){
        $this->factors['limit'] = "{$start}, {$end}";
        return $this;
    }

    /**
     * 设置PAGE条件
     * @param $page
     * @param $pageRow
     * @return $this
     */
    function page($page, $pageRow){
        $start = ($page - 1) * $pageRow;
        return $this->limit($start, $pageRow);
    }

    /**
     * 执行SELECT操作
     * @return array
     * @throws \Exception
     */
    function select(){
        $data = array();
        $result = $this->query($this->build('select'));
        while($row = $result->fetch_assoc()){
            array_push($data, $row);
        }
        return $data;
    }

    /**
     * 执行FIND操作
     * @return array
     * @throws \Exception
     */
    function find(){
        $this->factors['limit'] = 1;
        $result = $this->query($this->build('select'));
        return $result->fetch_assoc();
    }

    /**
     * 执行COUNT操作
     * @param string $field
     * @return array|null
     * @throws \Exception
     */
    function count($field = 'id'){
        $this->factors['fields'] = "COUNT({$field}) as count_val";
        return $this->select();
    }

    /**
     * 执行SUM操作
     * @param $field
     * @return array|null
     * @throws \Exception
     */
    function sum($field){
        $this->factors['fields'] = "SUM({$field}) as sum_val";
        return $this->select();
    }

    /**
     * 设置自增
     * @param $field
     * @param int $step
     * @return bool|\mysqli_result
     * @throws \Exception
     */
    function setInc($field, $step = 1){
        return $this->update([$field=>"{$field} + {$step}"]);
    }

    /**
     * 设置自减
     * @param $field
     * @param int $step
     * @return bool|\mysqli_result
     * @throws \Exception
     */
    function setDec($field, $step = 1){
        return $this->update([$field=>"{$field} - {$step}"]);
    }

    /**
     * 插入数据
     * @param $data
     * @param bool $returnId
     * @return bool|\mysqli_result
     * @throws \Exception
     */
    function insert($data, $returnId = false){
        $fields = array();
        $values = array();
        foreach ($data as $key => $val){
            array_push($fields, $this->parseField($key));
            array_push($values, $this->parseValue($val));
        }
        $this->factors['fields'] = implode(',', $fields);
        $this->factors['values'] = implode(',', $values);
        $result = $this->query($this->build('insert'));
        if($returnId){
            $res = $this->query('SELECT last_insert_id() as id');
            return $res->fetch_assoc()['id'];
        }else{
            return $result;
        }
    }

    /**
     * 更新数据
     * @param array $data
     * @return bool|\mysqli_result
     * @throws \Exception
     */
    function update(array $data){
        $cache = array();
        foreach ($data as $key => $val){
            array_push($cache, $this->parseField($key).' = "'.$this->quote($val).'"');
        }
        $this->factors['update'] = implode(', ', $cache);
        $result = $this->query($this->build('update'));
        return $result;
    }

    /**
     * 删除数据
     * @return bool|\mysqli_result
     * @throws \Exception
     */
    function delete(){
        $result = $this->query($this->build('delete'));
        return $result;
    }

    /**
     * 开启事务
     * @access public
     * @return bool
     */
    function transaction(){
        return $this->connection->begin_transaction();
    }

    /**
     * 提交事务
     * @access public
     * @return bool
     */
    function commit(){
        return $this->connection->commit();
    }

    /**
     * 回滚事务
     * @access public
     * @return bool
     */
    function rollback(){
        return $this->connection->rollback();
    }

    /**
     * 执行查询
     * @param $sql
     * @return bool|\mysqli_result
     * @throws \Exception
     */
    function query($sql){
        $result = $this->connection->query($sql);
        if($this->connection->error){
            throw new Exception("DATABASE: {$this->connection->error}");
        }else{
            return $result;
        }
    }

    /**
     * 生成SQL语句
     * @param $action
     * @return string
     * @throws Exception
     */
    protected function build($action){
        switch ($action){
            case 'select':
                $sql = ["SELECT {$this->factors['fields']} FROM {$this->factors['table']}"];
                break;
            case 'update':
                $sql = ["UPDATE {$this->factors['table']} SET {$this->factors['update']}"];
                break;
            case 'delete':
                $sql = ["DELETE FROM {$this->factors['table']}"];
                break;
            case 'insert':
                $sql = ["INSERT INTO {$this->factors['table']} ({$this->factors['fields']}) VALUES ({$this->factors['values']})"];
                break;
            default: throw new Exception("DATABASE: Unknown Action.");
        }
        if(isset($this->factors['join'])){
            array_push($sql, implode(' ', $this->factors['join']));
        }
        if(isset($this->factors['where'])){
            array_push($sql, "WHERE {$this->factors['where']}");
        }
        if(isset($this->factors['group'])){
            array_push($sql, "GROUP BY {$this->factors['group']}");
        }
        if(isset($this->factors['order'])){
            array_push($sql, 'ORDER BY ' . implode(', ', $this->factors['order']));
        }
        if(isset($this->factors['limit'])){
            array_push($sql, "LIMIT {$this->factors['limit']}");
        }
        return implode(' ', $sql);
    }

}